import traceback

import pymysql


# 处理地图数据
#获取朝代名称，返回名称集合
def GetDynastyMap():
    sql = "select dynasty_name,dynasty_begin,dynasty_end from humantity_dynasty"
    cur2.execute(sql)
    datas = cur2.fetchall()
    dy_names =[]
    for data in datas:
        name = data[0]
        begin = data[1]
        end = data[2]
        dy_names.append(name)
    print(dy_names)
    return dy_names


#处理多边形数据
def ProcessGeoPolygon(dy_names):
    values=[]
    for dynasty in dy_names:
        sql1="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from v6_time_pref_pgn_utf_wgs84 a,humantity_dynasty b where b.dynasty_begin>a.beg_yr and b.dynasty_end>a.end_yr and b.dynasty_begin<a.end_yr and  b.dynasty_name='"+dynasty+"'"
        sql2="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from v6_time_pref_pgn_utf_wgs84 a,humantity_dynasty b where b.dynasty_begin>a.beg_yr and b.dynasty_end>a.end_yr and b.dynasty_begin<a.end_yr and  b.dynasty_name='"+dynasty+"'"
        sql3="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from v6_time_pref_pgn_utf_wgs84 a,humantity_dynasty b where b.dynasty_begin>a.beg_yr and b.dynasty_end<a.end_yr and b.dynasty_name='"+dynasty+"'"
        sql4="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from v6_time_pref_pgn_utf_wgs84 a,humantity_dynasty b where b.dynasty_begin<a.beg_yr and b.dynasty_end>a.end_yr and b.dynasty_name='"+dynasty+"'"
        union=" UNION ALL "
        sql = sql1 +'\n'+union+'\n'+sql2 +'\n'+union+'\n'+sql3 +'\n'+union+'\n'+sql4
        print(sql)
        cur2.execute(sql)
        datas = cur2.fetchall()
        for data in datas:
            gid = data[0]
            name_py = data[1]
            name_ch = data[2]
            name_ft = data[3]
            x_coor = data[4]
            y_coor = data[5]
            pers_loc = data[6]
            type_py = data[7]
            type_ch = data[8]
            lev_rank = data[9]
            beg_yr = data[10]
            beg_rule = data[11]
            end_yr = data[12]
            end_rule = data[13]
            note_id = data[14]
            obj_type = data[15]
            sys_id = data[16]
            geo_src = data[17]
            compiler = data[18]
            gecomplr = data[19]
            checker = data[20]
            ent_date = data[21]
            beg_chg_ty = data[22]
            end_chg_ty = data[23]
            geom = data[24]
            dynasty_name = data[25]
            dynasty_begin = data[26]
            dynasty_end = data[27]
            value=(name_py,name_ch,name_ft,x_coor,y_coor,pers_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,geom,dynasty_name,dynasty_begin,dynasty_end)
            values.append(value)
            if len(values)>100:
                insert = "insert into geo_polygon(name_py,name_ch,name_ft,x_coor,y_coor,pers_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,geom,dynasty_name,dynasty_begin,dynasty_end) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                cur2.executemany(insert,values)
                conn2.commit()
                values=[]
    insert = "insert into geo_polygon(name_py,name_ch,name_ft,x_coor,y_coor,pers_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,geom,dynasty_name,dynasty_begin,dynasty_end) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cur2.executemany(insert,values)
    conn2.commit()

#处理点数据
def ProcessGeoPoint(dy_names):
    values=[]
    for dynasty in dy_names:
        sql1="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from geo_point_tmp a,humantity_dynasty b where b.dynasty_begin>a.beg_yr and b.dynasty_end>a.end_yr and b.dynasty_begin<a.end_yr and  b.dynasty_name='"+dynasty+"'"
        sql2="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from geo_point_tmp a,humantity_dynasty b where b.dynasty_begin>a.beg_yr and b.dynasty_end>a.end_yr and b.dynasty_begin<a.end_yr and  b.dynasty_name='"+dynasty+"'"
        sql3="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from geo_point_tmp a,humantity_dynasty b where b.dynasty_begin>a.beg_yr and b.dynasty_end<a.end_yr and b.dynasty_name='"+dynasty+"'"
        sql4="select a.*,b.dynasty_name,b.dynasty_begin,b.dynasty_end from geo_point_tmp a,humantity_dynasty b where b.dynasty_begin<a.beg_yr and b.dynasty_end>a.end_yr and b.dynasty_name='"+dynasty+"'"
        union=" UNION ALL "
        sql = sql1 +'\n'+union+'\n'+sql2 +'\n'+union+'\n'+sql3 +'\n'+union+'\n'+sql4
        cur2.execute(sql)
        datas = cur2.fetchall()
        for data in datas:
            name_py = data[1]
            name_ch = data[2]
            name_ft = data[3]
            x_coor = data[4]
            y_coor = data[5]
            pres_loc = data[6]
            type_py = data[7]
            type_ch = data[8]
            lev_rank = data[9]
            beg_yr = data[10]
            beg_rule = data[11]
            end_yr = data[12]
            end_rule = data[13]
            note_id = data[14]
            obj_type = data[15]
            sys_id = data[16]
            geo_src = data[17]
            compiler = data[18]
            gecomplr = data[19]
            checker = data[20]
            ent_date = data[21]
            beg_chg_ty = data[22]
            end_chg_ty = data[23]
            dynasty_name = data[24]
            dynasty_begin = data[25]
            dynasty_end = data[26]
            value=(name_py,name_ch,name_ft,x_coor,y_coor,pres_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,dynasty_name,dynasty_begin,dynasty_end)
            values.append(value)
            try:
                if len(values)>500:
                    print(len(values))
                    insert = "insert into geo_point(name_py,name_ch,name_ft,x_coor,y_coor,pres_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,dynasty_name,dynasty_begin,dynasty_end) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                    cur2.executemany(insert,values)
                    conn2.commit()
                    print("提交500")
                    values=[]
            except Exception:
                traceback.print_exc()
    insert = "insert into geo_point(name_py,name_ch,name_ft,x_coor,y_coor,pres_loc,type_py,type_ch,lev_rank,beg_yr,beg_rule,end_yr,end_rule,note_id,obj_type,sys_id,geo_src,compiler,gecomplr,checker,ent_date,beg_chg_ty,end_chg_ty,dynasty_name,dynasty_begin,dynasty_end) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cur2.executemany(insert,values)
    conn2.commit()


if __name__ == '__main__':
    try:
        conn1 = pymysql.Connect(host='10.170.128.56',
                                port=3306,
                                user='root',
                                passwd='123456',
                                db='humantity_nlp',
                                charset='utf8mb4')
        cur1 = conn1.cursor()

        conn2 = pymysql.Connect(host='10.170.128.56',
                                port=3306,
                                user='root',
                                passwd='123456',
                                db='humantity',
                                charset='utf8mb4')
        cur2 = conn2.cursor()

        ProcessGeoPoint(GetDynastyMap())

    except:
        cur1.close()
        conn1.close()
        cur2.close()
        conn2.close()
